package com.cleaningcloud.cld.mapper;

import com.cleaningcloud.cld.domain.CldInsuranceStaff;
import com.cleaningcloud.cld.domain.dto.CldInsuranceStaffDto;
import com.cleaningcloud.cld.domain.excelExport.InsuranceExcelExport;
import com.cleaningcloud.cld.domain.excelExport.InsuranceResignExcelExport;
import com.cleaningcloud.cld.domain.vo.CldInsuranceStaffVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 保险人员关联Mapper接口
 *
 * @author cleaningcloud
 * @date 2024-10-24
 */
public interface CldInsuranceStaffMapper {
    /**
     * 查询保险人员关联
     *
     * @param insuranceStaffId 保险人员关联主键
     * @return 保险人员关联
     */
    CldInsuranceStaff selectCldInsuranceStaffByInsuranceStaffId(Long insuranceStaffId);


    /**
     * 新增保险人员关联
     *
     * @param cldInsuranceStaff 保险人员关联
     * @return 结果
     */
    int insertCldInsuranceStaff(CldInsuranceStaff cldInsuranceStaff);

    /**
     * 修改保险人员关联
     *
     * @param cldInsuranceStaff 保险人员关联
     * @return 结果
     */
    int updateCldInsuranceStaff(CldInsuranceStaff cldInsuranceStaff);

    /**
     * 删除保险人员关联
     *
     * @param insuranceStaffId 保险人员关联主键
     * @return 结果
     */
    int deleteCldInsuranceStaffByInsuranceStaffId(Long insuranceStaffId);

    /**
     * 批量删除保险人员关联
     *
     * @param insuranceStaffIds 需要删除的数据主键集合
     * @return 结果
     */
    int deleteCldInsuranceStaffByInsuranceStaffIds(Long[] insuranceStaffIds);


    /**
     * 保险关联人员操作
     *
     * @param list
     * @return
     */
    int batchInsert(List<CldInsuranceStaff> list);


    /**
     * 历史操作记录统计
     *
     * @param cldInsuranceStaffDto
     * @return
     */
    List<Map<String, Object>> selectCldInsuranceStaffStatisticsList(CldInsuranceStaffDto cldInsuranceStaffDto);


    /**
     * 历史操作记录明细
     *
     * @param cldInsuranceStaffDto
     * @return
     */
    List<CldInsuranceStaffVo> selectCldInsuranceStaffList(CldInsuranceStaffDto cldInsuranceStaffDto);


    @Select("SELECT staff_name as staffName,staff_idcard as staffIdcard, staff_sex as staffSex,\n" +
            "    TIMESTAMPDIFF(YEAR, STR_TO_DATE(SUBSTRING(staff_idcard, 7, 8), '%Y%m%d'), CURDATE()) AS staffAge from cld_staff where insurance_id is null and staff_status = 2")
    List<InsuranceExcelExport> notInInsuranceStaffList();


    @Select("SELECT s.staff_name as staffName,s.staff_idcard as staffIdcard, s.staff_sex as staffSex,i.insurance_odd as insuranceOdd,\n" +
            "    TIMESTAMPDIFF(YEAR, STR_TO_DATE(SUBSTRING(s.staff_idcard, 7, 8), '%Y%m%d'), CURDATE()) AS staffAge from cld_staff s \n" +
            "\t\tleft join cld_insurance i on i.insurance_id = s.insurance_id\n" +
            "\t\twhere s.insurance_id is not null and s.staff_status = 3")
    List<InsuranceResignExcelExport> resignInsuranceStaffList();
}
